package com.geek.jdbc.work2;
import com.geek.jdbc.utils.JDBCUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/*
 *@Date 2025/11/17
 *@Author wangpeng
 *@Description TODO
 */
public class CustomersDao {
    //添加客户
    public int insert(Customers cus){
        //1.获取连接对象
        Connection conn = JDBCUtils.getConn();
        PreparedStatement ppst = null;

        try {
            //2.预编译SQL，获取语句对象
            String sql = "insert into customers values(?,?,?,?)";
            ppst = conn.prepareStatement(sql,PreparedStatement.RETURN_GENERATED_KEYS);
            //3.给?占位符传值
            ppst.setString(1 , cus.getId());
            ppst.setString(2 , cus.getName());
            ppst.setString(3 , cus.getEmail());
            ppst.setDate(4,new java.sql.Date(cus.getBirthday().getTime()));
            //4.执行 增、删、改 操作
            int i = ppst.executeUpdate();
            return i;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            //5.关闭资源
            JDBCUtils.close(ppst,conn,null);
        }
    }
    //修改指定客户姓名
    public int updateNameById(String name,String id){
        //1.获取连接对象
        Connection conn = JDBCUtils.getConn();
        PreparedStatement ppst = null;
        try {
            //2.预编译SQL，获取语句对象
            String sql = "update customers set name = ? where id = ?";
            ppst = conn.prepareStatement(sql);
            //3.给?占位符传值
            ppst.setString(1 , name);
            ppst.setString(2 , id);
            //4.执行 增、删、改 操作
            int i = ppst.executeUpdate();
            return i;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            //5.关闭资源
            JDBCUtils.close(ppst,conn,null);
        }
    }
    //查询所有客户信息
    public List<Customers> selectAll(){
        List<Customers> list = new ArrayList<>();
        //1.获取连接对象
        Connection conn = JDBCUtils.getConn();
        PreparedStatement ppst = null;
        ResultSet rs = null;

        try {
            //2.预编译SQL，获取语句对象
            String sql = "select * from customers";
            ppst = conn.prepareStatement(sql);
            //3.执行查询操作，获取结果集
            rs = ppst.executeQuery();
            //4.处理结果集
            while (rs.next()){
                String id = rs.getString("id");
                String name = rs.getString("name");
                String email = rs.getString("email");
                Date birthday = rs.getDate("birthday");
                Customers customers = new Customers(id, name, email, birthday);
                list.add(customers);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally {
            //5.关闭资源
            JDBCUtils.close(ppst,conn,rs);
        }
        return list;
    }
    //根据编号查询
    public Customers selectById(String id){
        //1.获取连接对象
        Connection conn = JDBCUtils.getConn();
        PreparedStatement ppst = null;
        ResultSet rs = null;
        Customers cus = null;
        try {
            //2.预编译SQL，获取语句对象
            String  sql = "select * from customers where id = ?";
            ppst = conn.prepareStatement(sql);
            ppst.setString(1,id);
            //3.执行查询操作，获取结果集
            rs = ppst.executeQuery();
            //4.处理结果集
            while (rs.next()){
                String name = rs.getString("name");
                String email = rs.getString("email");
                Date birthday = rs.getDate("birthday");
                cus = new Customers(id, name, email, birthday);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return cus;
    }
    //根据姓名查询
    public Customers selectByName(String name){
        //1.获取连接对象
        Connection conn = JDBCUtils.getConn();
        PreparedStatement ppst = null;
        ResultSet rs = null;
        Customers cus = null;

        try {
            //2.预编译SQL，获取语句对象
            String sql = "select * from customers where name = ?";
            ppst = conn.prepareStatement(sql);
            ppst.setString(1,name);
            //3.执行查询操作，获取结果集
            rs = ppst.executeQuery();
            //4.处理结果集
            while (rs.next()){
                String id = rs.getString("id");
                String email = rs.getString("email");
                Date birthday = rs.getDate("birthday");
                cus = new Customers(id, name, email, birthday);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return cus;
    }
}
